﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DatabaseCore.Dao.DWH
{
    public class DwhHelper
    {
        private SqlConnection connection;
        private string connectionString = ConfigurationManager.ConnectionStrings["dwhNawaEntities"].ConnectionString ??
            @"data source=.\sqlexpress;initial catalog=dwhNawa;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;";

        public DwhHelper()
        {
            connection = new SqlConnection(connectionString);

        }

        public DataTable GetVmsa140_product_basic(string matno, string matName, string matNameEng)
        {
            SqlCommand cmd = new SqlCommand("select * from vmsa140_product_basic where mflagdel != 'x'", connection);
            DataTable dt = new DataTable();
            using (SqlDataAdapter a = new SqlDataAdapter(cmd))
            {
                a.Fill(dt);
            }

            if (!string.IsNullOrEmpty(matno))
            {
                var d2 = dt.AsEnumerable().Where(d => d.Field<string>("mmatno").ToLower().Contains(matno));
                if (d2 != null && d2.Count() > 0)
                {
                    dt = d2.CopyToDataTable();
                }
            }

            if (!string.IsNullOrEmpty(matName))
            {
                var d2 = dt.AsEnumerable().Where(d => d.Field<string>("mmatthai").ToLower().Contains(matName));
                if (d2 != null && d2.Count() > 0)
                {
                    dt = d2.CopyToDataTable();
                }
            }

            if (!string.IsNullOrEmpty(matNameEng))
            {
                var d2 = dt.AsEnumerable().Where(d => d.Field<string>("mmateng").ToLower().Contains(matNameEng));
                if (d2 != null && d2.Count() > 0)
                {
                    dt = d2.CopyToDataTable();
                }
            }

            return dt;
        }

        public DataTable GetVmsa302_pipe_prodcate_product()
        {
            SqlCommand cmd = new SqlCommand("select * from vmsa302_pipe_prodcate_product", connection);
            DataTable dt = new DataTable();
            using (SqlDataAdapter a = new SqlDataAdapter(cmd))
            {
                a.Fill(dt);
            }

            return dt;
        }

        public DataTable GetVmsa160_movtype()
        {
            SqlCommand cmd = new SqlCommand("select * from vmsa160_movtype", connection);
            DataTable dt = new DataTable();
            using (SqlDataAdapter a = new SqlDataAdapter(cmd))
            {
                a.Fill(dt);
            }

            return dt;
        }



        public DataTable GetVmsa593_billdata_0280()
        {
            SqlCommand cmd = new SqlCommand("select * from vmsa593_billdata_0280", connection);
            DataTable dt = new DataTable();
            using (SqlDataAdapter a = new SqlDataAdapter(cmd))
            {
                a.Fill(dt);
            }

            return dt;
        }

        public DataTable GetVmsa144_product_convunit()
        {
            SqlCommand cmd = new SqlCommand("select * from vmsa144_product_convunit", connection);
            DataTable dt = new DataTable();
            using (SqlDataAdapter a = new SqlDataAdapter(cmd))
            {
                a.Fill(dt);
            }

            return dt;
        }
    }
}
